/*******************************************************************************
 * Copyright (c) 2011 Jovansonlee B. Cesar.
 * All rights reserved. This program and the accompanying materials
 * are made available under the terms of the GNU Public License v2.0
 * which accompanies this distribution, and is available at
 * http://www.gnu.org/licenses/old-licenses/gpl-2.0.html
 * 
 * Contributors:
 *     Jovansonlee B. Cesar - initial API and implementation
 ******************************************************************************/
package com.ivanceras.client.db;

import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

import com.google.code.gwt.database.client.Database;
import com.google.code.gwt.database.client.SQLError;
import com.google.code.gwt.database.client.SQLResultSet;
import com.google.code.gwt.database.client.SQLTransaction;
import com.google.code.gwt.database.client.StatementCallback;
import com.google.code.gwt.database.client.TransactionCallback;
import com.ivanceras.shared.db.AsyncDBCallback;
import com.ivanceras.shared.db.DAO;
import com.ivanceras.shared.db.DB_Rdbms;
import com.ivanceras.shared.db.Filter;
import com.ivanceras.shared.db.IDatabaseAsync;
import com.ivanceras.shared.db.Join;
import com.ivanceras.shared.db.ModelDef;
import com.ivanceras.shared.db.Order;
import com.ivanceras.shared.db.SchemaTable;
import com.ivanceras.shared.exception.DataTypeException;
import com.ivanceras.shared.exception.DatabaseException;

public class DB_Html5 extends DB_Rdbms implements IDatabaseAsync{

	Database db = null;

	public DB_Html5(String dbName) {
		if(Database.isSupported()){
			db = Database.openDatabase(dbName, "1.0", "Ivanceras Database", 1000000000);
		}
		else{
			try {
				throw new DatabaseException("html 5 database not supported on this browser");
			} catch (DatabaseException e) {
				e.printStackTrace();
			}
		}
	}

	@Override
	public void beginTransaction() throws DatabaseException {
		throw new DatabaseException("Not Implemented in Asynchronous Database versions");
	}

	@Override
	public void commitTransaction() throws DatabaseException {
		throw new DatabaseException("Not Implemented in Asynchronous Database versions");
	}

	@Override
	public void rollbackTransaction() throws DatabaseException {
		throw new DatabaseException("Not Implemented in Asynchronous Database versions");
	}

	@Override
	public boolean isTransacted() throws DatabaseException {
		throw new DatabaseException("Not Implemented in Asynchronous Database versions");
	}

	@Override
	public DAO[] select(ModelDef model, Join join, Filter[] filters,
			Integer offset, Integer limit, Boolean doCount, Order[] orders)
	throws DatabaseException {
		throw new DatabaseException("Not Implemented in Asynchronous Database versions");
	}

	@Override
	public boolean createModel(ModelDef model) throws DatabaseException {
		throw new DatabaseException("Not Implemented in Asynchronous Database versions");
	}

	@Override
	public DAO insert(DAO dao, ModelDef model) throws DatabaseException {
		throw new DatabaseException("Not Implemented in Asynchronous Database versions");
	}

	@Override
	public DAO update(DAO dao, ModelDef model, Filter[] filters)
	throws DatabaseException {
		throw new DatabaseException("Not Implemented in Asynchronous Database versions");
	}

	@Override
	public int delete(ModelDef model, Filter[] filters)
	throws DatabaseException {
		throw new DatabaseException("Not Implemented in Asynchronous Database versions");
	}

	@Override
	public int empty(ModelDef model) throws DatabaseException {
		throw new DatabaseException("Not Implemented in Asynchronous Database versions");
	}

	@Override
	public boolean drop(ModelDef model) throws DatabaseException {
		throw new DatabaseException("Not Implemented in Asynchronous Database versions");
	}

	@Override
	public boolean isClosed() {
		try {
			throw new DatabaseException("Not Implemented in Asynchronous Database versions");
		} catch (DatabaseException e) {
			e.printStackTrace();
		}
		return false;
	}

	@Override
	public boolean reset() {
		try {
			throw new DatabaseException("Not Implemented in Asynchronous Database versions");
		} catch (DatabaseException e) {
			e.printStackTrace();
		}
		return false;
	}

	@Override
	public Integer getTotalRecords(String schema, String modelName)
	throws DatabaseException {
		try {
			throw new DatabaseException("Not Implemented in Asynchronous Database versions");
		} catch (DatabaseException e) {
			e.printStackTrace();
		}
		return -1;
	}

	@Override
	public ModelDef getModelMetaData(String schema, String tableName)
	throws DatabaseException {
		throw new DatabaseException("Not Implemented in Asynchronous Database versions");
	}

	@Override
	public SchemaTable[] getTableNames(String schema, String tablePattern)
	throws DatabaseException {
		throw new DatabaseException("Not Implemented in Asynchronous Database versions");
	}

	//====================================================================================
	//
	//
	//				Async version
	//
	//
	//====================================================================================


	@Override
	public void beginTransaction(AsyncDBCallback<Void> callback) {
		// TODO Auto-generated method stub

	}

	@Override
	public void commitTransaction(AsyncDBCallback<Void> callback) {
		// TODO Auto-generated method stub

	}

	@Override
	public void rollbackTransaction(AsyncDBCallback<Void> callback) {
		// TODO Auto-generated method stub

	}

	@Override
	public void isTransacted(AsyncDBCallback<Boolean> callback) {
		// TODO Auto-generated method stub

	}

	@Override
	public void createModel(ModelDef model, AsyncDBCallback<Boolean> callback) {
		// TODO Auto-generated method stub

	}

	@Override
	public void delete(ModelDef model, Filter[] filters,
			AsyncDBCallback<Integer> callback) {
		// TODO Auto-generated method stub

	}

	@Override
	public void drop(ModelDef model, AsyncDBCallback<Boolean> callback) {
		// TODO Auto-generated method stub

	}

	@Override
	public void empty(ModelDef model, AsyncDBCallback<Integer> callback) {
		// TODO Auto-generated method stub

	}

	@Override
	public void getModelMetaData(String tableName,
			AsyncDBCallback<ModelDef> callback) {
		// TODO Auto-generated method stub

	}

	@Override
	public void getTableNames(String schema, String tablePattern,
			AsyncDBCallback<String[]> callback) {
		// TODO Auto-generated method stub

	}

	@Override
	public void getTotalRecords(String schema, String modelName,
			AsyncDBCallback<Integer> callback) {
		// TODO Auto-generated method stub

	}

	@Override
	public void insert(DAO dao, ModelDef model, AsyncDBCallback<DAO> callback) {
		// TODO Auto-generated method stub

	}

	@Override
	public void isClosed(AsyncDBCallback<Boolean> callback) {
		// TODO Auto-generated method stub

	}

	@Override
	public void reset(AsyncDBCallback<Boolean> callback) {
		// TODO Auto-generated method stub

	}
	
	@Override
	public void selectOne(final ModelDef model, Join join, Filter[] filters,
			Integer offset, Integer limit, Boolean doCount, Order[] orders,
			final AsyncDBCallback<DAO> callback) throws DatabaseException {
		select(model, join, filters, offset, limit, doCount, orders, new AsyncDBCallback<DAO[]>() {

			@Override
			public void onFailure(Throwable caught) {
				callback.onFailure(caught);
			}

			@Override
			public void onSuccess(DAO[] result) {
				callback.onSuccess(result[0]);
			}
		});
	}

	
	@Override
	public void select(final ModelDef model, Join join, Filter[] filters,
			Integer offset, Integer limit, Boolean doCount, Order[] orders,
			final AsyncDBCallback<DAO[]> callback) throws DatabaseException {
		
		final String sql = buildSelectStatement(model, join, filters, offset, limit, doCount, orders);
		final Object[] parameters = getFilterValues(filters);
		executeSQL(sql, parameters, new StatementCallback<Row>() {

			@Override
			public void onSuccess(SQLTransaction transaction, SQLResultSet<Row> resultSet) {
				List<DAO> daoList = new ArrayList<DAO>();
				String[] attributes = model.getAttributes();
				for(Row r : resultSet.getRows()){
					DAO dao = new DAO(model.getModelName());
					for(String att : attributes){
						Object value = r.get_Value(att.replace("\"", "").toLowerCase());
						dao.set_Value(att, value);
					}
					daoList.add(dao);
				}
				callback.onSuccess(daoList.toArray(new DAO[daoList.size()]));
			}

			@Override
			public boolean onFailure(SQLTransaction transaction, SQLError error) {
				callback.onFailure(new Throwable("Transaction Failure, in executing sql ["+sql+"] "+(parameters != null ? Arrays.asList(parameters) : null)));
				return false;
			}
		});
	}

	@Override
	public void update(DAO dao, final ModelDef model, Filter[] filters,
			final AsyncDBCallback<DAO> callback) throws DatabaseException {
		final String sql = buildUpdateStatement(model, filters);
		String schema = model.getNamespace();
		schema = getDBElementName(model,schema);

		String[] columns = model.getAttributes();
		String autoIncrementColumn = model.getGeneratedAttribute();
		autoIncrementColumn = getDBElementName(model, autoIncrementColumn);

		final List<Object> parameters = new ArrayList<Object>();
		String[] dataTypes = model.getDataTypes();
		for(int i = 0; i < columns.length; i++){
			if(autoIncrementColumn != null && autoIncrementColumn.equals(columns[i])){
				;
			}else{
				Object value = convertDaoValueToDB(dao, columns[i]);
				parameters.add(value);//add the values of the dao as first parameters
			}
		}
		Object[] filterParams = getFilterValues(filters);
		if(filterParams != null && filterParams.length > 0){
			for(int i = 0; i < filterParams.length; i++){
				parameters.add(filterParams[i]);//then add the filter parameters
			}
		}
		Object[] param = new Object[parameters.size()];
		for(int i = 0; i < parameters.size(); i++){
			param[i] = parameters.get(i); //change to array
		}
		executeSQL(sql, param, new StatementCallback<Row>() {

			@Override
			public void onSuccess(SQLTransaction transaction,
					SQLResultSet<Row> resultSet) {
				List<DAO> daoList = new ArrayList<DAO>();
				String[] attributes = model.getAttributes();
				for(Row r : resultSet.getRows()){
					DAO dao = new DAO(model.getModelName());
					for(String att : attributes){
						Object value = r.get_Value(att.replace("\"", "").toLowerCase());
						dao.set_Value(att, value);
					}
					daoList.add(dao);
				}
				DAO dao = null;
				if(daoList != null && daoList.size() > 0){
					dao = daoList.get(0);
				}
				callback.onSuccess(dao);
			}

			@Override
			public boolean onFailure(SQLTransaction transaction, SQLError error) {
				callback.onFailure(new Throwable("Transaction Failure, in executing sql ["+sql+"] "+(parameters != null ? Arrays.asList(parameters) : null)));
				return false;
			}
		});
	}

	public Boolean executeSQL(final String sql, final Object[] parameters, final StatementCallback<Row> callback)
	throws DatabaseException {
		db.transaction(new TransactionCallback() {
			public void onTransactionStart(SQLTransaction tx) {
				tx.executeSql(sql, parameters, callback);
			}
			public void onTransactionFailure(SQLError error) {
				String msg = "There is an error executing SQL ["+sql+"]"+(parameters!=null?Arrays.asList(parameters):null);
				System.err.println(msg);
				try {
					throw new DatabaseException(msg);
				} catch (DatabaseException e) {
					e.printStackTrace();
				}
			}
			public void onTransactionSuccess() {

				System.out.println("sql sucessfully executed!");
			}
		});
		return true;
	}

	public Boolean executeUpdateSQL(final String sql, final Object[] parameters, final StatementCallback<Row> callback)
	throws DatabaseException {
		db.transaction(new TransactionCallback() {
			public void onTransactionStart(SQLTransaction tx) {
				tx.executeSql(sql, parameters, callback);
			}
			public void onTransactionFailure(SQLError error) {
				String msg = "There is an error executing SQL ["+sql+"]"+(parameters!=null?Arrays.asList(parameters):null);
				System.err.println(msg);
				try {
					throw new DatabaseException(msg);
				} catch (DatabaseException e) {
					e.printStackTrace();
				}
			}
			public void onTransactionSuccess() {

				System.out.println("sql sucessfully executed!");
			}
		});
		return true;
	}


	@Override
	protected String getAutoIncrementColumnConstraint() {
		return "PRIMARY KEY AUTOINCREMENT";
	}

	@Override
	public String getEquivalentDBDataType(String dataType) throws DataTypeException{
		if(dataType.equals("java.lang.String")){
			return "text";
		}
		else if(dataType.equals("java.lang.Double")){
			return "real";
		}
		else if(dataType.equals("java.math.BigDecimal")){
			return "numeric";
		}
		else if(dataType.equals("java.lang.Float")){
			return "real";
		}
		else if(dataType.equals("java.lang.Integer")){
			return "integer";
		}
		else if(dataType.equals("java.lang.Long")){
			return "integer";
		}
		else if(dataType.equals("java.lang.Boolean")){
			return "numeric";
		}
		else if(dataType.equals("java.lang.Byte[]")){
			return "none";
		}
		else if(dataType.equals("java.util.Date")){
			return "numeric";
		}
		else{
			throw new DataTypeException("No Equivalent Database Data type found for ["+dataType+"]");
		}
	}

	/**
	 * This is just used for building the ModelDefinition based on the database tables
	 */
	public String getEquivalentJavaDataType(String dataType) throws DataTypeException{
		if(dataType.startsWith("text")){//include limitized datatype such as character varying(255)
			return "java.lang.String";
		}
		else if(dataType.startsWith("numeric")){
			return "java.math.BigDecimal";
		}
		else if(dataType.startsWith("integer")){
			return "java.lang.Integer";
		}
		else if(dataType.startsWith("int")){
			return "java.lang.Integer";
		}
		else if(dataType.startsWith("bigint")){
			return "java.lang.Long";
		}
		else if(dataType.startsWith("character")){
			return "java.lang.String";
		}
		else if(dataType.equals("boolean")){
			return "java.lang.Boolean";
		}
		else if(dataType.equals("date")){
			return "java.util.Date";
		}
		else if(dataType.equals("text")){
			return "java.lang.String";
		}
		else if(dataType.equals("name")){
			return "java.lang.String";
		}
		else if(dataType.startsWith("timestamp")){
			return "java.util.Date";
		}
		else if(dataType.equals("bytea")){
			return "java.lang.Byte[]";
		}
		else{
			throw new DataTypeException("No Equivalent Java Data type found for ["+dataType+"]");
		}

	}

	@Override
	protected Object convertDaoValueToDB(DAO dao, String column){
		column = column.replace("\"", "");
		if(dao == null) return null;
		Object value = dao.get_Value(column);
		if(value == null){
			return null;
		}
		if(value.getClass().equals(java.util.Date.class)){
			Timestamp time = new Timestamp(((Date)value).getTime());
			return time;
		}
		else{
			return value;
		}
	}


}
